<?php
namespace MyApp\Application\Model;

use DinhThi\MVC\Model\MySQLModel;
class MatHangModel extends MySQLModel{
    public function __construct(){
        parent::connect();
    }

    public function ThongTinMatHang($id){
        $sth = $this->DB->prepare('select mathang.id, mathang.mamathang, mathang.ten, mathang.donvitinh, mathang.soluong, mathang.ghichu, banggia.giagoc, banggia.giaban
                                    from mathang_data mathang
                                    inner join mathang_giaban banggia
                                    on banggia.mamathang = mathang.id
                                    where mathang.id = :id and banggia.hieuluc = 1
                                    ');
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetchAll();
    }
    public function ThongTinMatHangTheoMa($id){
        $sth = $this->DB->prepare('select mathang.id, mathang.mamathang, mathang.ten, mathang.donvitinh, mathang.soluong, mathang.ghichu, banggia.giagoc, banggia.giaban
                                    from mathang_data mathang
                                    inner join mathang_giaban banggia
                                    on banggia.mamathang = mathang.id
                                    where mathang.mamathang = :id and banggia.hieuluc = 1
                                    ');
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetchAll();
    }

    public function NhapKho($ghichu, $listMatHang, $listSoluong){
        $sth = $this->DB->prepare("insert into `nhapkho` (ghichu) values (:ghichu)");
        $sth->execute(array(
            ':ghichu' => $ghichu
        ));


        $sth = $this->DB->prepare('select LAST_INSERT_ID() as ID');
        $sth->execute();
        $id = $sth->fetch();
        $id = $id['ID'];

        foreach ($listMatHang as $key => $value){
            $this->LuuChiTietNhapKho($id, $value, $listSoluong[$key]);
            //$this->ThayDoiSoLuong($value, $listSoluong[$key]);
        }

        $sth = $this->DB->prepare('select * from nhapkho where id = :id');
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetch();
    }

    public function LuuChiTietNhapKho($id, $mathang, $soluong){
        $sth = $this->DB->prepare("
            insert into nhapkho_chitiet (manhapkho, mamathang, soluong) values (:id, :mathang, :soluong)
        ");
        $sth->execute(
            array(
                ':id'       => $id,
                ':mathang'  => $mathang,
                ':soluong'  => $soluong
            )
        );
    }

    public function ThayDoiSoLuong($mathang, $soluong){
        $sth = $this->DB->prepare('UPDATE mathang_data SET soluong = (soluong + :soluong) WHERE id = :id');
        $sth->execute(
            array(
                ':id'       => $mathang,
                ':soluong'  => $soluong
            )
        );
    }

    public function XemChiTietNhapKho($id){
        $sth = $this->DB->prepare('select kho.ghichu, mathang.id, mathang.ten, mathang.donvitinh, chitiet.soluong
                                    from nhapkho_chitiet chitiet
                                    inner join nhapkho kho
                                    on kho.id = chitiet.manhapkho
                                    inner join mathang_data mathang
                                    on mathang.id = chitiet.mamathang
                                    where chitiet.manhapkho = :id'
        );
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetchAll();
    }

    public function DanhSachNhapKho(){
        $sth = $this->DB->prepare('select * from nhapkho order by id desc');
        $sth->execute();
        return $sth->fetchAll();
    }

    public function XoaHieuLucGia($id){
        $sth = $this->DB->prepare('update mathang_giaban set hieuluc = 0 where mamathang = :id and hieuluc = 1');
        $sth->execute(
            array(
                ':id' => $id
            )
        );
    }

    public function ThemDieuChinhGia($mamathang, $giagoc, $giaban){
        //Xoa hieu luc
        $this->XoaHieuLucGia($mamathang);


        $sth = $this->DB->prepare("insert into mathang_giaban (mamathang, giagoc, giaban) values (:mamathang, :giagoc, :giaban)");
        $sth->execute(array(
            'mamathang' => $mamathang,
            'giagoc'    => $giagoc,
            'giaban'    => $giaban
        ));


        $sth = $this->DB->prepare('select LAST_INSERT_ID() as ID');
        $sth->execute();
        $id = $sth->fetch();
        $id = $id['ID'];

        $sth = $this->DB->prepare('select * from mathang_giaban where id = :id');
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetch();
    }

    public function ThongTinDieuChinhGia($id){
        $sth = $this->DB->prepare('select mathang.ten, mathang.donvitinh, ban.giagoc, ban.giaban, ban.thoigianchinh, ban.hieuluc from mathang_giaban ban
        inner join  mathang_data mathang on mathang.id = ban.mamathang
        where ban.mamathang = :id order by ban.id desc');
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetchAll();
    }

    public function KiemTraHangTonKho($min, $max){
        $sth = $this->DB->prepare("select id, mamathang, ten, donvitinh, soluong, ghichu
                                    from mathang_data
                                    where soluong >= :min and soluong <= :max and type = 'default'
                                    ");
        $sth->execute(
            array(
                ':min' => $min,
                ':max' => $max
            )
        );

        return $sth->fetchAll();
    }

    public function TatCaMatHang(){
        $sth = $this->DB->prepare("select id, mamathang, ten, donvitinh, soluong, ghichu
                                    from mathang_data
                                    where type = 'default'
                                    ");
        $sth->execute();

        return $sth->fetchAll();
    }

} 